package com.bss;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.logging.FileHandler;
import java.util.logging.Logger;

public class Migration {

	static FileHandler fh;

	static Logger logger = Logger.getAnonymousLogger();

	private static final Date TODAY = new Date();

	private static final Date HYPOTHETICAL_DOB = new GregorianCalendar(2050, 1, 1).getTime();

	private Connection oldDbConnection;

	private Connection newDbConnection;

	private Properties properties;

	private String[] args;

	public Migration(String[] args) {
		this.args = args;
	}

	public void openConnection() throws Exception {
		Migration.logger.info("Opening connection");
		Class.forName("com.mysql.jdbc.Driver");
		this.oldDbConnection = DriverManager.getConnection(this.properties.getProperty("oldDbUrl"),
				this.properties.getProperty("user"), this.properties.getProperty("password"));
		this.newDbConnection = DriverManager.getConnection(this.properties.getProperty("newDbUrl"),
				this.properties.getProperty("user"), this.properties.getProperty("password"));
		this.newDbConnection.setAutoCommit(false);
		Migration.logger.info("Opening connection complete");
	}

	protected void init() throws Exception {
		if ((this.args == null) || (this.args.length < 4)) {
			throw new Exception("Error: Missing arguments !\nUsage: java " + this.getClass().getName()
					+ " oldDbUrl newDbUrl user password");
		}
		this.properties = new Properties();
		this.properties.setProperty("oldDbUrl", this.args[0]);
		this.properties.setProperty("newDbUrl", this.args[1]);
		this.properties.setProperty("user", this.args[2]);
		this.properties.setProperty("password", this.args[3]);

		this.openConnection();

	}

	protected void preProcess() throws Exception {
		this.init();
	}

	protected void postProcess() throws SQLException {
		Migration.logger.info("Closing connection");
		// this.newDbConnection.rollback();
		this.newDbConnection.commit();
		this.newDbConnection.close();
		this.oldDbConnection.close();
		Migration.logger.info("Closing connection complete");

	}

	final public void migrate() {
		try {
			this.preProcess();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {

			this.process();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {

			this.postProcess();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	int findBuilding(String name, Connection newDbConnection) throws SQLException {
		String sql = "Select id from Building where name = ?";
		PreparedStatement ps = newDbConnection.prepareStatement(sql);
		ps.setString(1, name);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected void process() throws Exception {
		this.insertBuildings();

		Map<Integer, Integer> roomMap = this.insertRooms();

		Map<Integer, Integer> bedMap = this.insertDormitories();

		this.insertIdCardTypes();

		this.insertProfessions();

		this.insertRelationships();

		this.insertMedicalProblems();

		this.insertMaterialTypes();

		// oldPatientToNewPatientMap will hold old to new patient mapping
		Map<Integer, Integer> oldPatientToNewPatientMap = this.insertPatients();

		// will hold old transaction to new admission id
		Map<Integer, Integer> transactionToAdmissionMap = new HashMap<Integer, Integer>();

		// patientToAdmissionMap will hold old patient id to new admission id
		Map<Integer, Integer> patientToAdmissionMap = this.insertAdmissions(oldPatientToNewPatientMap, transactionToAdmissionMap);

		this.insertAttendents(oldPatientToNewPatientMap, patientToAdmissionMap);

		this.insertRoomOccupancy(roomMap, bedMap, transactionToAdmissionMap);

		this.insertMaterialAllocations(transactionToAdmissionMap);

		this.insertUtensilSetAllocations(patientToAdmissionMap);

	}

	protected void insertUtensilSetAllocations(Map<Integer, Integer> patientToAdmissionMap) throws SQLException {
		String sqlOld = "Select * from utensil_allocation";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		int paymentType = this.findPaymentType("Utensil Deposit");

		while ((r != null) && r.next()) {
			Migration.logger.info("Utensil Allocation: Updating for Patient ID " + r.getInt("patientId") + ", AdmissionID: "
					+ patientToAdmissionMap.get(r.getInt("patientId")));

			if (patientToAdmissionMap.get(r.getInt("patientId")) == null) {
				Migration.logger.info("Utensil Allocation: Skipping as admission for this patient not found Patient ID "
						+ r.getInt("patientId"));
				continue;
			}
			String sqlNew = "Update Admission set idMaterialSet = ? where id = ?";
			PreparedStatement psNew = this.newDbConnection.prepareStatement(sqlNew);

			// old utensil set is same as new one
			psNew.setInt(1, r.getInt("allocationType"));
			psNew.setInt(2, patientToAdmissionMap.get(r.getInt("patientId")));
			psNew.executeUpdate();
			psNew.close();

			Migration.logger.info("Utensil Allocation: Inserting payment for Patient ID " + r.getInt("patientId"));

			sqlNew = "Insert into Payment(idPaymentType,idPayable,dateStart,dateEnd,amount,refund,date) values(?,?,?,?,?,?,?)";
			psNew = this.newDbConnection.prepareStatement(sqlNew);

			psNew.setInt(1, paymentType);
			psNew.setInt(2, patientToAdmissionMap.get(r.getInt("patientId")));
			psNew.setDate(3, r.getDate("allocationDate"));
			psNew.setDate(4, r.getDate("allocationDate"));
			psNew.setInt(5, r.getInt("amountDeposited"));
			psNew.setBoolean(6, false);
			psNew.setDate(7, r.getDate("allocationDate"));

			psNew.executeUpdate();
			psNew.close();

			if (r.getDate("returnDate") != null) {
				sqlNew = "Insert into Payment(idPaymentType,idPayable,dateStart,dateEnd,amount,refund,date) values(?,?,?,?,?,?,?)";
				psNew = this.newDbConnection.prepareStatement(sqlNew);

				psNew.setInt(1, paymentType);
				psNew.setInt(2, patientToAdmissionMap.get(r.getInt("patientId")));
				psNew.setDate(3, r.getDate("returnDate"));
				psNew.setDate(4, r.getDate("returnDate"));
				psNew.setInt(5, r.getInt("amountDeposited"));
				psNew.setBoolean(6, true);
				psNew.setDate(7, r.getDate("returnDate"));

				psNew.executeUpdate();
				psNew.close();
			}

		}
		r.close();
		psOld.close();
		// //
	}

	protected void insertMaterialAllocations(Map<Integer, Integer> transactionToAdmissionMap) throws SQLException {
		String sqlOld = "Select m.*,c.transactionId as transactionId from materialallocation m, check_in_out c where c.materialsAllocationId = m.allocationId";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		String sqlNew = "Insert into MaterialAllocation(idAdmission,idMaterialType,dateAllocated,instances,date) values(?,?,?,?,?)";
		PreparedStatement psMaterialAllocation = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {
			Migration.logger.info("Material Allocation: Inserting for Allocation ID " + r.getInt("allocationId") + " for Checkin: "
					+ r.getInt("transactionId") + ", AdmissionID: " + transactionToAdmissionMap.get(r.getInt("transactionId")));

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Single Bed Sheet"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfBS"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Double Bed Sheet"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfDBS"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Single Bed Mosquito Net"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfMN"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Double Bed Mosquito Net"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfDBN"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Bucket"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfBK"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Mug"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfMG"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Lock & Key"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfLK"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType("Milk Ticket"));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("noOfMlkTkt"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType(r.getString("others1type")));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("others1"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

			psMaterialAllocation.setInt(1, transactionToAdmissionMap.get(r.getInt("transactionId")));
			psMaterialAllocation.setInt(2, this.findMaterialType(r.getString("others2type")));
			psMaterialAllocation.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.setInt(4, r.getInt("others2"));
			psMaterialAllocation.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psMaterialAllocation.executeUpdate();
			psMaterialAllocation.clearParameters();

		}
		psMaterialAllocation.close();
		r.close();
		psOld.close();
	}

	protected void insertAttendents(Map<Integer, Integer> oldPatientToNewPatientMap, Map<Integer, Integer> patientToAdmissionMap)
			throws SQLException {
		String sqlOld = "Select * from attendent_record";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		String sqlNew = "Insert into Person(sex,idIdCardType,idCardNumber,date,idProfession,dateOfBirth)" + " values(?,?,?,?,?,?)";
		PreparedStatement psPerson = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

		sqlNew = "Insert into Attendent(id,name,idPatient,idRelationship) values(?,?,?,?)";
		PreparedStatement psAttendent = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

		sqlNew = "Insert into CurrentAttendent(idAdmission,idAttendent,dateOfArrival,date) values(?,?,?,?)";
		PreparedStatement psCurrentAttendent = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {

			int personId = this.findAttendentForPatient(r.getString("name"), oldPatientToNewPatientMap.get(r.getInt("patientId")));

			if (personId == -1) {

				Migration.logger.info("Attendent: Inserting for patient " + r.getInt("patientId") + ", AttendentId: "
						+ r.getInt("attendentId"));

				psPerson.setString(1, this.findSexByRelationShip(r.getString("relationship")));
				psPerson.setInt(2,
						r.getString("idType") == null ? this.findIdCardType("N/A") : this.findIdCardType(r.getString("idType")));
				psPerson.setString(3, r.getString("idNumber"));
				psPerson.setDate(4, new java.sql.Date(Migration.TODAY.getTime()));

				psPerson.setInt(5, this.findProfession("OTHERS"));
				psPerson.setDate(6, new java.sql.Date(Migration.HYPOTHETICAL_DOB.getTime()));
				psPerson.executeUpdate();

				ResultSet keys = psPerson.getGeneratedKeys();
				if ((keys != null) && keys.next()) {
					personId = keys.getInt(1);
					keys.close();
				}

				psPerson.clearParameters();

				psAttendent.setInt(1, personId);
				psAttendent.setString(2, r.getString("name"));
				psAttendent.setInt(3, oldPatientToNewPatientMap.get(r.getInt("patientId")));
				psAttendent.setInt(4, this.findRelationship(r.getString("relationship")));
				psAttendent.executeUpdate();
				psAttendent.clearParameters();
			}

			if (r.getBoolean("isPresent")) {

				Migration.logger.info("OldPatient to New " + oldPatientToNewPatientMap.get(r.getInt("patientId")) + " New  ");

				psCurrentAttendent.setInt(1, patientToAdmissionMap.get(r.getInt("patientId")));
				psCurrentAttendent.setInt(2, personId);
				// Since we do not have exact data
				psCurrentAttendent.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
				psCurrentAttendent.setDate(4, new java.sql.Date(Migration.TODAY.getTime()));
				psCurrentAttendent.executeUpdate();
			}
		}

		psPerson.close();
		psAttendent.close();
		psCurrentAttendent.close();
	}

	protected Map<Integer, Integer> insertAdmissions(Map<Integer, Integer> patientMap,
			Map<Integer, Integer> transactionToAdmissionMap) throws SQLException {

		Map<Integer, Integer> patientAdmissionMap = new HashMap<Integer, Integer>();

		String sqlOld = "Select c.*,p.`tatacaseno` as tatacaseno, p.medicalProblem as medicalProblem from check_in_out c, patient_record p where c.`patientId` = p.`patientId`";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		int paymentType = this.findPaymentType("Security Deposit");

		String sqlNew = "Insert into Payable(date) values(?)";
		PreparedStatement psPayable = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

		PreparedStatement psAdmissions = this.prepareStatementForAdmissions();

		sqlNew = "Update Patient set addressLine2 = ? where id = ? ";
		PreparedStatement psUpdatePatient = this.newDbConnection.prepareStatement(sqlNew);

		sqlNew = "Insert into Payment(idPaymentType,idPayable,dateStart,dateEnd,amount,refund,date) values(?,?,?,?,?,?,?)";
		PreparedStatement psPayment = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {
			int patientId = -1;
			if (patientMap.get(patientId = r.getInt("patientId")) == null) {
				Migration.logger.info("Admission: No patient entry found for this admission : " + r.getInt("transactionId"));
				continue;
			}

			Migration.logger.info("Admission: Inserting for " + r.getInt("patientId"));

			psPayable.setDate(1, new java.sql.Date(Migration.TODAY.getTime()));
			psPayable.executeUpdate();

			int idPayable = -1;
			ResultSet keys = psPayable.getGeneratedKeys();
			if ((keys != null) && keys.next()) {
				idPayable = keys.getInt(1);
				patientAdmissionMap.put(patientId, idPayable);
				keys.close();
			}

			psPayable.clearParameters();

			psAdmissions.setInt(1, idPayable);
			psAdmissions.setInt(2, patientMap.get(patientId));
			psAdmissions.setDate(3, r.getDate("checkInTime"));
			psAdmissions.setDate(4, r.getDate("checkOutTime"));
			psAdmissions.setDate(5, r.getDate("dateOfStay"));
			psAdmissions.setDate(6, r.getDate("extDateOfStay"));
			psAdmissions.setBoolean(7, r.getDate("checkOutTime") == null);
			psAdmissions.setString(8, this.left(r.getString("tatacaseno"), 10));
			psAdmissions.setInt(9, this.findMedicalProblem(r.getString("medicalProblem")));
			psAdmissions.executeUpdate();

			psAdmissions.clearParameters();

			psUpdatePatient.setString(1, r.getString("townOfLastDeparture"));
			psUpdatePatient.setInt(2, patientId);
			psUpdatePatient.executeUpdate();

			psUpdatePatient.clearParameters();

			transactionToAdmissionMap.put(r.getInt("transactionId"), idPayable);

			// Lets insert deposit payment

			Migration.logger.info("Admission Deposit: Inserting payment for Patient ID " + r.getInt("patientId"));

			psPayment.setInt(1, paymentType);
			psPayment.setInt(2, idPayable);
			psPayment.setDate(3, r.getDate("checkInTime"));
			psPayment.setDate(4, r.getDate("checkInTime"));
			psPayment.setInt(5, r.getInt("depositAmount"));
			psPayment.setBoolean(6, false);
			psPayment.setDate(7, r.getDate("checkInTime"));

			psPayment.executeUpdate();
			psPayment.clearParameters();

			if (r.getDate("checkOutTime") != null) {

				psPayment.setInt(1, paymentType);
				psPayment.setInt(2, idPayable);
				psPayment.setDate(3, r.getDate("checkOutTime"));
				psPayment.setDate(4, r.getDate("checkOutTime"));
				psPayment.setInt(5, r.getInt("depositAmount"));
				psPayment.setBoolean(6, true);
				psPayment.setDate(7, r.getDate("checkOutTime"));

				psPayment.executeUpdate();
				psPayment.clearParameters();
			}

			// //

			Migration.logger.info("Admission: Inserted for " + r.getInt("patientId") + " TransactionId: "
					+ r.getInt("transactionId") + ", AdmissionId: " + idPayable);
		}
		psPayable.close();
		psAdmissions.close();
		psUpdatePatient.close();
		psPayment.close();

		return patientAdmissionMap;
	}

	protected String left(String str, int i) {
		if (str == null) {
			return "";
		}
		if (str.length() > 10) {
			return str.substring(0, 10);
		}
		return str;
	}

	protected PreparedStatement prepareStatementForAdmissions() throws SQLException {
		return this.newDbConnection
				.prepareStatement(
						"Insert into Admission(id,idPatient,dateOfAdmission,dateOfExit,dateFrom,dateTo,active,exitInited,caseno,idMedicalProblem) values(?,?,?,?,?,?,?,false,?,?)",
						Statement.RETURN_GENERATED_KEYS);
	}

	protected void insertRoomOccupancy(Map<Integer, Integer> roomMap, Map<Integer, Integer> bedMap,
			Map<Integer, Integer> transactionToAdmissionMap) throws SQLException {

		String sqlOld = "Select * from room_allocation";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		String sqlNew;

		sqlNew = "Insert into Payable(date) values(?)";
		PreparedStatement psPayable = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

		sqlNew = "Insert into RoomBooking(id,idAdmission,idRoom,checkInDate,checkOutDate,rate,totalPayment) values(?,?,?,?,?,0,?)";
		PreparedStatement psRoomBooking = this.newDbConnection.prepareStatement(sqlNew);

		sqlNew = "Insert into BedBooking(id,idAdmission,idBed,checkInDate,checkOutDate,rate,totalPayment) values(?,?,?,?,?,0,?)";
		PreparedStatement psBedBooking = this.newDbConnection.prepareStatement(sqlNew);

		PreparedStatement psBooking = null;

		while ((r != null) && r.next()) {

			if (transactionToAdmissionMap.get(r.getInt("transactionId")) == null) {
				Migration.logger.info("Room Occupancy: Cannot inserting for TransactionId: " + r.getInt("transactionId")
						+ " as no admission exists ! There was possibly no patient for this transaction");
				continue;
			}

			Migration.logger.info("Room Occupancy: Inserting for TransactionId: " + r.getInt("transactionId") + ", AdmissionId: "
					+ transactionToAdmissionMap.get(r.getInt("transactionId")));

			int idAdmission = transactionToAdmissionMap.get(r.getInt("transactionId"));

			psPayable.setDate(1, new java.sql.Date(Migration.TODAY.getTime()));
			psPayable.executeUpdate();

			int idPayable = -1;
			ResultSet keys = psPayable.getGeneratedKeys();
			if ((keys != null) && keys.next()) {
				idPayable = keys.getInt(1);
				keys.close();
			}

			psPayable.clearParameters();

			if (r.getInt("domId") == 0) {
				psBooking = psRoomBooking;
				psBooking.setInt(3, roomMap.get(r.getInt("roomId")));
			} else {
				psBooking = psBedBooking;
				psBooking.setInt(3, bedMap.get(r.getInt("domId")));
			}
			psBooking.setInt(1, idPayable);
			psBooking.setInt(2, idAdmission);
			psBooking.setDate(4, r.getDate("dateofstay"));
			psBooking.setDate(5, r.getDate("dateofcheckout"));
			psBooking.setInt(6, r.getInt("amount"));
			psBooking.executeUpdate();
			psBooking.clearParameters();
		}
	}

	protected void insertBuildings() throws SQLException {
		String sqlOld = "Select buildingName , max(floorNo) as floors from rooms group by buildingName";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		String sqlNew = "Insert into Building(name,floors,date) values(?,?,?)";
		PreparedStatement psNew = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {
			Migration.logger.info("Building: Inserting for " + r.getString("buildingName"));

			psNew.setString(1, r.getString("buildingName"));
			psNew.setInt(2, r.getInt("floors") == 0 ? 1 : r.getInt("floors"));
			psNew.setDate(3, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();
			psNew.clearParameters();

		}
		psNew.close();
		r.close();
		psOld.close();
	}

	protected Map<Integer, Integer> insertRooms() throws SQLException {

		Map<Integer, Integer> oldToNewRoomMap = new HashMap<Integer, Integer>();
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		PreparedStatement psNew;
		ResultSet r;
		// Rooms
		sqlOld = "Select * from rooms";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("Rooms: Inserting for " + r.getString("roomNo"));
			sqlNew = "Insert into Room(roomNumber,idBuilding,floor,numberOfBeds,dormitory,ac,rate,date) "
					+ " values(?,?,?,0,?,0,0,?)";
			psNew = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

			psNew.setString(1, r.getString("roomNo"));
			psNew.setInt(2, this.findBuilding(r.getString("buildingName"), this.newDbConnection));
			psNew.setInt(3, r.getInt("floorNo"));
			psNew.setBoolean(4, r.getBoolean("isDom"));
			psNew.setDate(5, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();

			int roomId = -1;
			ResultSet keys = psNew.getGeneratedKeys();
			if ((keys != null) && keys.next()) {
				roomId = keys.getInt(1);
				oldToNewRoomMap.put(r.getInt("roomId"), roomId);
				keys.close();
			}
		}
		return oldToNewRoomMap;
	}

	protected Map<Integer, Integer> insertDormitories() throws SQLException {

		Map<Integer, Integer> oldToNewBedMap = new HashMap<Integer, Integer>();
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		PreparedStatement psNew;
		ResultSet r;
		// Dormitories
		sqlOld = "select d.domId as domId, d.domNo as domNo, d.isLower as isLower,r.roomNo as roomNo from doms d, rooms r where r.roomId = d.roomId";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("Dormitories: Inserting for " + r.getString("domId"));

			sqlNew = "Insert into Bed(idRoom,idBedType,bedNumber,date) " + " values(?,?,?,?)";
			psNew = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

			psNew.setInt(1, this.findRoom(r.getString("roomNo")));
			psNew.setInt(2, r.getInt("isLower") == 1 ? 1 : 2);
			psNew.setInt(3, r.getInt("domNo"));
			psNew.setDate(4, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();

			int bedId = -1;
			ResultSet keys = psNew.getGeneratedKeys();
			if ((keys != null) && keys.next()) {
				bedId = keys.getInt(1);
				oldToNewBedMap.put(r.getInt("domId"), bedId);
				keys.close();
			}
		}
		return oldToNewBedMap;
	}

	protected void insertIdCardTypes() throws SQLException {

		Set<String> set = new HashSet<String>();
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		PreparedStatement psNew;
		ResultSet r;
		// ID Card Type
		sqlOld = "select distinct idCardtype from patient_record";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("Card Types: Inserting for " + r.getString("idCardType"));

			sqlNew = "Insert into IdCardType(name,date)" + " values(?,?)";

			psNew = this.newDbConnection.prepareStatement(sqlNew);
			psNew.setString(1, r.getString("idCardType"));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();

			set.add(r.getString("idCardType"));
		}

		// ID Card Type
		sqlOld = "select distinct idType from attendent_record";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {

			if (!set.contains(r.getString("idType"))) {
				Migration.logger.info("Card Types: Inserting for " + r.getString("idType"));
				sqlNew = "Insert into IdCardType(name,date)" + " values(?,?)";

				psNew = this.newDbConnection.prepareStatement(sqlNew);
				psNew.setString(1, r.getString("idType"));
				psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
				psNew.executeUpdate();

			}
		}
	}

	protected void insertRelationships() throws SQLException {
		String sqlOld = "Select distinct relationship from attendent_record";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		String sqlNew = "Insert into Relationship(name,date) values(?,?)";
		PreparedStatement psNew = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {
			Migration.logger.info("Relationship: Inserting for " + r.getString("relationship"));

			psNew.setString(1, r.getString(1));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();
			psNew.clearParameters();
		}

		psNew.close();
		r.close();
		psOld.close();
	}

	protected void insertMaterialTypes() throws SQLException {

		Set<String> set = new HashSet<String>();

		String sqlOld = "select distinct others1type from materialallocation";
		PreparedStatement psOld = this.oldDbConnection.prepareStatement(sqlOld);

		ResultSet r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("Material Type: Inserting for " + r.getString("others1type"));

			String sqlNew = "Insert into MaterialType(name,date) values(?,?)";
			PreparedStatement psNew = this.newDbConnection.prepareStatement(sqlNew);

			psNew.setString(1, r.getString("others1type"));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();
			psNew.close();

			set.add(r.getString("others1type"));
		}

		r.close();
		psOld.close();

		sqlOld = "select distinct others2type from materialallocation";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {

			if (set.contains(r.getString("others2type"))) {
				continue;
			}

			Migration.logger.info("Material Type: Inserting for " + r.getString("others2type"));

			String sqlNew = "Insert into MaterialType(name,date) values(?,?)";
			PreparedStatement psNew = this.newDbConnection.prepareStatement(sqlNew);

			psNew.setString(1, r.getString("others2type"));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();

			psNew.close();
		}

		r.close();
		psOld.close();
	}

	protected void insertProfessions() throws SQLException {
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		PreparedStatement psNew;
		ResultSet r;
		// Profession
		sqlOld = "select distinct profession from patient_record";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("Profession: Inserting for " + r.getString("profession"));

			sqlNew = "Insert into Profession(name,date)" + " values(?,?)";
			psNew = this.newDbConnection.prepareStatement(sqlNew);
			psNew.setString(1, r.getString("profession"));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();
		}
	}

	protected void insertMedicalProblems() throws SQLException {
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		PreparedStatement psNew;
		ResultSet r;
		// Medical Problem
		sqlOld = "select distinct medicalProblem from patient_record";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		while ((r != null) && r.next()) {
			Migration.logger.info("MedicalProblem: Inserting for " + r.getString("medicalProblem"));

			sqlNew = "Insert into MedicalProblem(name,date)" + " values(?,?)";
			psNew = this.newDbConnection.prepareStatement(sqlNew);
			psNew.setString(1, r.getString("medicalProblem"));
			psNew.setDate(2, new java.sql.Date(Migration.TODAY.getTime()));
			psNew.executeUpdate();
		}
	}

	protected Map<Integer, Integer> insertPatients() throws SQLException {

		Map<Integer, Integer> map = new HashMap<Integer, Integer>();
		String sqlOld;
		PreparedStatement psOld;
		String sqlNew;
		ResultSet r;
		sqlOld = "select * from patient_record";
		psOld = this.oldDbConnection.prepareStatement(sqlOld);

		r = psOld.executeQuery();

		sqlNew = "Insert into Person(sex,idIdCardType,idCardNumber,idProfession,photo,date,dateOfBirth)" + " values(?,?,?,?,?,?,?)";
		PreparedStatement psPerson = this.newDbConnection.prepareStatement(sqlNew, Statement.RETURN_GENERATED_KEYS);

		sqlNew = "Insert into Patient(id,firstName,middleName,lastName,addressLine1,phone,idReligion,idCountry,idState,idPlace)"
				+ " values(?,?,?,?,?,?,1,1,1,1)";
		PreparedStatement psPatient = this.newDbConnection.prepareStatement(sqlNew);

		while ((r != null) && r.next()) {

			byte[] image = this.fetchImage(r.getBinaryStream("photograph"));

			InputStream iis = null;

			if (image != null) {
				iis = new ByteArrayInputStream(image);
			}

			Migration.logger.info("Patient: Inserting for Patient(" + (r.getInt("patientId")) + "): "
					+ r.getString("patientFirstName") + " " + r.getString("patientMiddleName") + " "
					+ r.getString("patientLastName"));

			psPerson.setString(1, r.getString("sex").toUpperCase());
			psPerson.setInt(2, this.findIdCardType(r.getString("idCardType")));
			psPerson.setString(3, r.getString("idCardNumber"));
			int idProfession = this.findProfession(r.getString("profession"));
			psPerson.setInt(4, idProfession == 0 ? this.findProfession("OTHERS") : idProfession);
			if (image != null) {
				psPerson.setBinaryStream(5, iis, image.length);
			} else {
				psPerson.setBinaryStream(5, null, 0);
			}
			psPerson.setDate(6, new java.sql.Date(Migration.TODAY.getTime()));
			psPerson.setDate(7, new java.sql.Date(Migration.HYPOTHETICAL_DOB.getTime()));
			psPerson.executeUpdate();

			int personId = -1;
			ResultSet keys = psPerson.getGeneratedKeys();
			if ((keys != null) && keys.next()) {
				personId = keys.getInt(1);
				map.put(r.getInt("patientId"), personId);
				keys.close();
			}

			psPerson.clearParameters();

			// Religion must be updated

			psPatient.setInt(1, personId);
			psPatient.setString(2, r.getString("patientFirstName"));
			psPatient.setString(3, r.getString("patientMiddleName"));
			psPatient.setString(4, r.getString("patientLastName"));
			String address = r.getString("address");
			psPatient.setString(5, address == null ? "" : address);
			psPatient.setString(6, r.getString("contactNo"));
			psPatient.executeUpdate();
			psPatient.clearParameters();
		}
		return map;
	}

	protected int findAttendentForPatient(String name, Integer idPatient) throws SQLException {
		String sql = "Select id from Attendent where name = ? and idPatient = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, name);
		ps.setInt(2, idPatient);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return -1;
	}

	protected String findSexByRelationShip(String relationship) throws SQLException {
		return relationship.equalsIgnoreCase("Brother") || relationship.equalsIgnoreCase("Father")
				|| relationship.equalsIgnoreCase("Uncle") ? "M" : "F";
	}

	protected byte[] fetchImage(InputStream photoStream) {

		if (photoStream == null) {
			return null;
		}

		try {
			int size = 0;
			byte[] bytearray = new byte[1024];

			ByteArrayOutputStream bos = new ByteArrayOutputStream(1024 * 8);

			while ((size = photoStream.read(bytearray)) != -1) {
				bos.write(bytearray, 0, size);
			}
			return bos.toByteArray();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	protected int findRoom(String roomNumber) throws SQLException {
		String sql = "Select id from Room where roomNumber = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, roomNumber);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findMaterialType(String materialType) throws SQLException {
		String sql = "Select id from MaterialType where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, materialType);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findPaymentType(String paymentType) throws SQLException {
		String sql = "Select id from PaymentType where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, paymentType);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findMedicalProblem(String medicalProblem) throws SQLException {
		String sql = "Select id from MedicalProblem where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, medicalProblem);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findRelationship(String relationship) throws SQLException {
		String sql = "Select id from Relationship where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, relationship);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findProfession(String profession) throws SQLException {
		String sql = "Select id from Profession where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, profession);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	protected int findIdCardType(String idCardType) throws SQLException {
		String sql = "Select id from IdCardType where name = ?";
		PreparedStatement ps = this.newDbConnection.prepareStatement(sql);
		ps.setString(1, idCardType);

		ResultSet r = ps.executeQuery();

		if ((r != null) && r.next()) {
			return r.getInt(1);
		}
		return 0;
	}

	public static void main(String[] args) {
		new Migration(args).migrate();
	}
}
